package pipeline;

import com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException;
import model.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import us.codecraft.webmagic.ResultItems;
import us.codecraft.webmagic.Task;
import us.codecraft.webmagic.pipeline.Pipeline;
import util.Config;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author bruce.liu
 * @Description 使用Mysql Jdbc插入数据库的不同数据库中
 */
public class MysqlTestPipeline implements Pipeline{
    private Logger logger = LoggerFactory.getLogger(getClass());

    private Connection conn;
    public MysqlTestPipeline() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        this.conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/crawler_test?useSSL=false", "root", "liukai123");
    }
    @Override
    public void process(ResultItems resultItems, Task task) {
        List<Map<String,Object>> results = resultItems.get("infos");
//        System.out.println("size is :" + results.size());
        try {
            Statement state=this.conn.createStatement();
            String sql = "insert into test(name,create_date,task_id,address,status,min_value)" +
                    "values(?,?,?,?,?,?)";
            PreparedStatement pstmt=this.conn.prepareStatement(sql);//获得预置对象
            results.forEach(info -> {
                try {
                    Date d = (Date)info.get("createTime");
                    System.out.println("时间："+new java.sql.Time(d.getTime()));
                    pstmt.setString(1,info.get("name").toString());
//                    pstmt.setDate(2,new java.sql.Date(d.getTime()));
//                    pstmt.setTime(2,new java.sql.Time(d.getTime()));
                    pstmt.setObject(2,d);
                    pstmt.setString(3,info.get("taskId").toString());
                    pstmt.setString(4,info.get("address").toString());
                    pstmt.setInt(5,1);
                    String minValue = info.get("minValue").toString();
                    System.out.println("min-value is "+minValue);
                    pstmt.setBigDecimal(6,new BigDecimal(minValue));
                    pstmt.addBatch();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            });
            pstmt.executeBatch();
            conn.commit();
            conn.close();

        }
        catch ( MySQLIntegrityConstraintViolationException ee){
            logger.warn("已有记录");
        }catch (SQLException e){
            System.out.println(e);
        }

//        System.out.println(resultItems.get("")+"插入完成");
    }



}
